<?php
/**
 * Xoops Object CRUD handler
 * 
 * @copyright      RB Lin (xtheme@gmail.com>
 * @license          http://www.mozilla.org/MPL/MPL-1.1.html	Mozilla Public License Version 1.1
 * @author           RB Lin (xtheme@gmail.com>
 * @url                 http://xoops.tnc.edu.tw
 * @version         2.0
 */
 
class RbCrud extends XoopsObject
{

}

class RbCrudHandler extends XoopsObjectHandler
{
	var $tableName;     // Table name, 資料表名稱
	var $keyName;       // Primary key  auto_increment field name, 主欄位
	var $className;     // Class name
	var $orderBy;       // Defaule ORDER BY, 預設排序方式
	var $errors         = array();
	
	// Active Records
	
	var $ar_distinct    = FALSE;
	var $ar_select      = array();
	var $ar_join		= array();
	var $ar_where       = array();
	var $ar_groupby     = array();
	var $ar_having      = array();
	var $ar_orderby     = array();
	var $ar_limit       = FALSE;
	var $ar_offset      = FALSE;
	var $ar_data        = array();
	
	
	function __construct($db = NULL, $tableName = '', $className = '', $keyName = '', $orderBy = '')
	{
		$db = ( is_object($db) && is_a($db, 'XoopsDatabase') ) ? $db : $GLOBALS['xoopsDB'];
		$table = $db->prefix($tableName);
		$this->XoopsObjectHandler($db);
		
		$this->tableName = $table;
		$this->keyName = $keyName;
		$this->className = $className;
		$this->orderBy = $orderBy;
	}
	
	
	function RbCrudHandler($tableName = '', $className = '', $keyName = '', $orderBy = '')
	{
		$this->__construct($tableName, $className, $keyName, $orderBy);
	}
	
	// --------------------------------------------------------------------
	
	/**
	* Object Action Function
	* ORM (Object-relational mapping) 
	* use Object do CRUD
	* 物件操控方法, UD 前必須先建立物件
	*
	* Create    $handler->create() 
	* Read       $handler->get(), $handler->getObjects()
	* Update   $handler->insert()
	* Delete    $handler->delete()
	*/
	
	/**
	* Create Object
	* 建立 (宣告) 新物件
	*
	* @param	bool
	* @return	object
	*/
	function &create($isNew = TRUE)
	{
		$object =& new $this->className();
		if ($isNew === TRUE) {
			$object->setNew();
		}
		return $object;
	}
	
	
	/**
	* Get a Object by Primary key or other field
	* Only return first data
	* 藉由主鍵或其他欄位取得一筆物件化的資料
	* 若符合的資料超過一筆, 也只回傳第一筆物件
	*
	* @access	public
	* @param	string
	* @param	string	field name
	* @return	object
	*/
	function get($value = '', $field = '')
	{	
		$object = FALSE;
		
		if (!empty($value)) {
			if (empty($field)) {
				$field = $this->keyName;
				if(!$this->isExists($value)) {
					$this->setErrors(__CLASS__.'::'.__FUNCTION__.' primary key is exists');
					return FALSE;
				}
			}
			$clause = "`{$field}` = ".$this->db->quoteString($value);
			//$this->ar_where[] = $clause;
			$this->where($clause);
		} 
		
		if (!count($this->ar_where)) {
			$this->setErrors(__CLASS__.'::'.__FUNCTION__.' no where condition');
			return $object;
		}
		
		$sql = $this->compileSQL();
		
		if (!$result = $this->db->query($sql)) {
            return $object;
        }
		
		if ($this->db->getRowsNum($result) != 1) {
            return $object;
        }
		
		$object =& $this->create(false);
        $object->assignVars($this->db->fetchArray($result));
		
		return $object;
	}
	
	
	/**
	* Action
	* Return array of object or array data by search
	* 依照查詢條件回傳符合資料
	*
	* @param	bool	return object or array
	* @param	bool	use the Primary as key for the array
	* @return	array
	*/
	function &getAll($setWhere = NULL, $setOrderBy = NULL, $setLimit = NULL, $setOffset = NULL, $asObject = TRUE, $id_as_key = TRUE)
	{
		/*
		* Compatibility ver.1 start
		*/
		if ($setWhere || $setOrderBy || $setLimit || $setOffset) {
			
			$this->resetSQL();
			
			if ($setWhere) {
				$this->where($setWhere);
			}
			
			if ($setOrderBy) {
				$this->order_by($setOrderBy);
			}
			
			if ($setLimit) {
				$this->limit($setLimit);
			}
			
			if ($setOffset) {
				$this->offset($setOffset);
			}
		}
		/*
		* Compatibility ver.1 end
		*/
		
		$sql = $this->compileSQL();
		
		if (!$result = $this->db->query($sql)) {
			return FALSE;
		}
		
		$ret = array();
		if ($asObject) {
			while ($myrow = $this->db->fetchArray($result)) {
				$object =& $this->create(FALSE);
				$object->assignVars($myrow);
				if ( $id_as_key ) {
					$ret[$myrow[$this->keyName]] = $object;
				} else {
					$ret[] = $object;
				}
				unset($object);
			}
		} else {
			while ($myrow = $this->db->fetchArray($result)) {
				if ($id_as_key) {
					$ret[$myrow[$this->keyName]] = $myrow;
				} else {
					$ret[] = $myrow;
				}
			}
		}
		return $ret;
	}
	
	
	/**
	* Action
	* Return object data by search
	* 將查詢結果的每筆資料都物件化並以陣列回傳
	*
	* @param	bool	use the Primary as key for the array
	* @return	array of objects
	*/
	function getObjects($setWhere = NULL, $setOrderBy = NULL, $setLimit = NULL, $setOffset = NULL, $id_as_key = FALSE)
	{
		$ret = $this->getAll($setWhere, $setOrderBy, $setLimit, $setOffset, TRUE, FALSE);
		return $ret;
	}
	
	
	/**
	* Action
	* use Primary as key, some field as value, return array
	* 指定一個欄位, 回傳一組鍵為主鍵, 值為指定欄位的陣列
	*
	* @param	string	field name
	* @return	array
	*/
	function getList($identifier = '')
	{
		$ret = array();
		
		$this->ar_select[] = $this->keyName;
		$this->ar_select[] = $identifier;
		$sql = $this->compileSQL();
		
		if (!$result = $this->db->query($sql)) {
			return $ret;
		}
		
		$myts =& MyTextSanitizer::getInstance();
		while ($myrow = $this->db->fetchArray($result)) {
			$ret[$myrow[$this->keyName]] = $myts->htmlSpecialChars($myrow[$identifier]);
		}
		return $ret;
	}
	
	
	/**
	* Action
	* return Primary array by search
	* 將查詢結過回傳只有主鍵的陣列
	*
	* @return	array
	*/
	function getIds()
	{
		$ret = array();
		
		$this->ar_select[] = $this->keyName;
		$sql = $this->compileSQL();
		
		if (!$result = $this->db->query($sql)) {
			return $ret;
		}
		
		while ($myrow = $this->db->fetchArray($result)) {
			$ret[] = $myrow[$this->keyName];
		}
		return $ret;
	}
	
	
	/**
	* Insert or Update a Object data
	* 新增或更新一筆物件資料
	*
	* @param	object
	* @param	bool         test or not
	* @return	bool
	*/
	function insert(&$object, $force = TRUE)
	{
		if (!is_a($object, $this->className)) {
			return FALSE;
		}
		
		if (!$object->isDirty()) {
			return TRUE;
		}
		
		if (!$object->cleanVars()) {
			return FALSE;
		}
		
		$queryFunc = empty($force) ? 'query' : 'queryF';
		
		$insert_into = array();
		$insert_set  = array();
		$update_set  = array();
		foreach ( $object->cleanVars as $key => $val ) {
			${$key} = $val;
			$insert_into[] = sprintf('`%s`',$key);
			$insert_set[]  = $this->db->quoteString($val);
			$update_set[]  = sprintf('`%s`',$key).' = '.$this->db->quoteString($val);
		}
		$insert_into = implode(', ', $insert_into);
		$insert_set  = implode(', ', $insert_set);
		$update_set  = implode(', ', $update_set);
		
		if ($object->isNew()) {
			$sql  = 'INSERT INTO '.$this->tableName;
			$sql .= ' ('.$insert_into.') VALUES ('.$insert_set.')';

		} else {
			$sql  = 'UPDATE '.$this->tableName.' SET '.$update_set;
			$sql .= ' WHERE `'.$this->keyName.'` = '.$this->db->quoteString($object->getVar($this->keyName));
		}
		echo $sql;
		if (!$result = $this->db->{$queryFunc}($sql)) {
			$this->setErrors(__CLASS__.'::'.__FUNCTION__.' query fail');
			return FALSE;
		}
		
		if ( !$object->getVar($this->keyName) && $object_id = $this->db->getInsertId() ) {
			$object->assignVar($this->keyName, $object_id);
		}
		return TRUE;
	}
	
	
	/**
	* Delete a Object data
	* 刪除一筆物件資料
	*
	* @param	object
	* @param	bool         test or not
	* @return	bool
	*/
	function delete(&$object, $force = true)
	{
		if (!is_a($object, $this->className)) {
			$this->setErrors(__CLASS__.'::'.__FUNCTION__.' is not an object');
			return false;
		}
		$sql = sprintf("DELETE FROM %s WHERE ".$this->keyName." = %u", $this->tableName, $object->getVar($this->keyName));
		
		$queryFunc = empty($force) ? 'query' : 'queryF';
		
		if (!$result = $this->db->{$queryFunc}($sql)) {
			$this->setErrors(__CLASS__.'::'.__FUNCTION__.' query fail');
			return false;
		}
		return true;
	}
	
	
	// --------------------------------------------------------------------
	
	/**
	* Row Action Function
	* use Primary key do CRUD
	* 以主鍵作對單筆資料作更快速的操作
	*/
	
	
	/**
	* Insert a row data
	* 寫入一筆資料
	*
	* @param	array
	* @param	bool
	* @param	bool
	* @return	bool	link $this->insert
	*
	* $handler->insertRow($_POST)
	*/
	function insertRow($data, $force = TRUE, $not_gpc = FALSE)
	{
		$object =& $this->create(TRUE);
		$object->setVars($data, $not_gpc);
		return $this->insert($object, $force);
	}
	
	
	/**
	* Update a row data by Primary Key
	* 藉由主鍵更新一筆資料
	*
	* @param	array		request data
	* @param	integer	Primary Key, if request data without Primary Key
	* @param	bool
	* @param	bool
	* @return	bool		link $this->insert
	*
	* $handler->updateRow($_POST)
	*/
	function updateRow($data, $primary = 0, $force = TRUE, $not_gpc = FALSE)
	{
		$primary = intval($primary);
		
		if (!$primary) {
			if (array_key_exists($this->keyName, $data)) {
				$primary = $data[$this->keyName];
			} else {
				$this->setErrors(__CLASS__.'::'.__FUNCTION__.' need primary key');
				return FALSE;
			}
		}
		
		if( !$this->isExists($primary) ) {
			$this->setErrors(__CLASS__.'::'.__FUNCTION__.' primary key is exists');
			return FALSE;
		}
		
		$object =& $this->get($primary);
		$object->setVars($data, $not_gpc);
		return $this->insert($object, $force);
	}
	
	
	/**
	* Delete a row data by Primary Key
	* 藉由主鍵刪除一筆資料
	*
	* @param	integer	Primary Key
	* @return	bool
	*
	* $handler->deleteRow($_POST['id'])
	*/
	function deleteRow($primary = 0, $force = TRUE)
	{
		$primary = intval($primary);
		
		if( !$this->isExists($primary) ) {
			$this->setErrors(__CLASS__.'::'.__FUNCTION__.' primary key is exists');
			return FALSE;
		}
		$object = $this->get($primary);
		return $this->delete($object, $force);
	}
	
	
	// --------------------------------------------------------------------
	
	/**
	* Batch Action Function
	*  批次處理的方法
	*/
	
	/**
	* Delete data after Query set
	* 刪除符合條件的資料
	*
	* @param	bool
	* @param	bool
	* @return	bool
	*
	* $handler->where('status = 0')->order('create_time ASC')->limit('5')->deleteAll()
	*/
	function deleteAll($force = TRUE, $as_object = FALSE)
	{
		
		if ($as_object) {
			$objects = $this->getObjects();
			$num = 0;
			foreach (array_keys($objects) as $key) {
				$num += $this->delete($objects[$key], $force) ? 1 : 0;
			}
			unset($objects);
			return $num;
		}
		
		$queryFunc = empty($force) ? 'query' : 'queryF';
		
		if ( !is_array($this->ar_where) || !count($this->ar_where) ) {
			trigger_error(__CLASS__."::".__FUNCTION__." need $this->where", E_USER_WARNING);
			return FALSE;
		}
		
		$sql_override = 'DELETE FROM '.$this->tableName;
		$sql = $this->compileSQL($sql_override);
		
		if (!$this->db->{$queryFunc}($sql)) {
			$this->setErrors(__CLASS__."::".__FUNCTION__." query error<br />$sql<br />");
			return FALSE;
		}
		return TRUE;
	}
	
	
	/**
	* Update date after Query set
	* 更新符合條件的資料欄位
	*
	* @param 	array
	* @param	bool
	* @return	bool
	*
	* $handler->where('status = 1')->updateAll($_POST)
	*/
	function updateAll($data, $force = TRUE)
	{
		$queryFunc = empty($force) ? 'query' : 'queryF';
		
		if ( is_array($data) && count($data) > 0 ) {
			$set_arr = array();
			foreach ($data as $key => $val) {
				$update_set = "`{$key}` = ";
				if ( is_array($val) ) {
					$update_set .= $this->db->quoteString( implode(',', $val) );
				} else {
					$update_set .= $this->db->quoteString($val);
				}
				$set_arr[] = $update_set;
			}
			$set_clause = implode(', ', $set_arr);
		} else {
			$this->setErrors(__CLASS__."::".__FUNCTION__." no data input!!<br />");
			return FALSE;
		}
		
		$sql_override = 'UPDATE `'.$this->tableName.'` SET ' . $set_clause;
		$sql = $this->compileSQL($sql_override);
		
		if (!$this->db->{$queryFunc}($sql)) {
			$this->setErrors(__CLASS__."::".__FUNCTION__." query error<br />$sql<br />");
			return FALSE;
		}
		return TRUE;
    }
	
	
	// --------------------------------------------------------------------
	
	/**
	* Count Action Function
	* 查詢數量的方法
	*/
	
	/**
	* Count query result and return int
	* 依照查詢條件回傳符合的資料筆數
	*
	* $handler->where('cat = 1 AND type = 2')->getCount()
	*/
	function getCount($setWhere = NULL)
	{
		/*
		* Compatibility ver.1 start
		*/
		if ($setWhere) {
			
			$this->resetSQL();
			
			if ($setWhere) {
				$this->where($setWhere);
			}
		}
		/*
		* Compatibility ver.1 end
		*/
		
		$sql = $this->compileSQL();
		if (!$result = $this->db->query($sql)) {
			return 0;
		}
		$count = $this->db->getRowsNum($result);
		return $count;
	}
	
	
	/**
	* Check data is exists and return bool
	* 依照查詢條件回傳布林值
	*
	* @param	string
	* @param	string
	* @return	bool
	*
	*  $handler->where('id = 5')->isExists()
	*  $handler->isExists(5)
	*  $handler->isExists('7654321', 'isbn')
	*/
	function isExists($value= '', $field = '', $setWhere = NULL)
	{
		/*
		* Compatibility ver.1 start
		*/
		if (is_array($field)) {
			$setWhere = $field;
			$field = $this->keyName;
		}
		
		if ($setWhere) {
			
			$this->resetSQL();
			
			if ($setWhere) {
				$this->where($setWhere);
			}
		}
		/*
		* Compatibility ver.1 end
		*/
		
		if (!empty($value)) {
			if (empty($field)) {
				$field = $this->keyName;
			}
			$clause = "`{$field}` = ".$this->db->quoteString($value);
			//$this->ar_where[] = $clause;
			$this->where($clause);
		}
		
		if (!count($this->ar_where)) {
			return FALSE;
		}
		
		$sql = $this->compileSQL();
		if ($result = $this->db->query($sql)) {
			$count = $this->db->getRowsNum($result);
			if ( $count == 1 ){
				return TRUE;
			}
		}
		return FALSE;
	}
	
	// --------------------------------------------------------------------
	
	/**
	*  Field Action Function
	*  針對一個欄位的方法
	*/
	
	/**
	* Get a field value
	* 取得欄位值, 一次只能查詢一個欄位
	*
	* @param	string
	* @param	string	MIN, MAX, SUM, AVG
	* @return	string
	*/
	function getField($field, $type = '')
	{
		$field = FALSE;
		
		$type = (in_array(strtoupper(trim($type)), array('MIN', 'MAX', 'SUM', 'AVG'), TRUE)) ? $type : '';
		if ($type != '') {
			$selectFunc = 'select_'.$type;
			$this->{$selectFunc}($field);
		} else {
			$this->ar_select[] = $field;
		}
		
		if (count($this->ar_select) != 1) {
			return $field;
		}
		
		$sql = $this->compileSQL();
		$result = $this->db->query($sql);
		list($field) = $this->db->fetchRow($result);
		
		return $field;
	}
	
	
	/**
	* Dec a field value
	* If without $this-where() will update all data
	* 遞增欄位值
	* 請搭配 $this->wher() 設定更新條件, 否則將更新所有資料
	*
	* @param	string
	* @param	integer
	* @return	bool
	*
	* $handler->where('id = 7')->incField('views', 1)
	*/
	function incField($field, $step = 1)
	{
		$step = intval($step);
		$clause = "$field = $field + $step";
		$sql_override = 'UPDATE `'.$this->tableName.'` SET '.$clause;
		$sql = $this->compileSQL($sql_override);
		$result = $this->db->query($sql);
		if ($result) {
			return TRUE;
		}
		return FALSE;
	}
	
	/**
	* Dec a field value
	* If without $this-where() will update all data
	* 遞減欄位值
	* 請搭配 $this->wher() 設定更新條件, 否則將更新所有資料
	*
	* @param	string
	* @param	integer
	* @return	bool
	*
	* $handler->where('uid = 7')->decField('karma', 5)
	*/
	function decField($field, $step = 1)
	{
		$step = intval($step);
		$clause = "$field = $field - $step";
		$sql_override = 'UPDATE `'.$this->tableName.'` SET '.$clause;
		$sql = $this->compileSQL($sql_override);
		$result = $this->db->query($sql);
		if ($result) {
			return TRUE;
		}
		return FALSE;
	}
	
	
	// --------------------------------------------------------------------
	
	/**
	* SQL Action Function
	* Active Record
	*/
	
	
	/**
	* Generates a SELECT MIN(field) portion of a query
	* 取得指定欄位 "最小" 值
	*
	* @param	string
	* @param	string
	* @return	object
	*/
	function select_min($field, $alias)
	{
		if ( ! is_string($field) OR $field == '') {
			trigger_error(__CLASS__."::".__FUNCTION__." need field", E_USER_WARNING);
		}
		
		$alias = ($alias != '') ? $alias : $field;
		
		$str = 'MIN('.trim($field).') AS '.trim($alias);
		$this->select($str);
		return $this;
	}
	
	
	/**
	* Generates a SELECT MAX(field) portion of a query
	* 取得指定欄位 "最大" 值
	*
	* @param	string
	* @param	string
	* @return	object
	*/
	function select_max($field, $alias)
	{
		if ( ! is_string($field) OR $field == '') {
			trigger_error(__CLASS__."::".__FUNCTION__." need field", E_USER_WARNING);
		}

		$alias = ($alias != '') ? $alias : $field;
		
		$str = 'MAX('.trim($field).') AS '.trim($alias);
		$this->select($str);
		return $this;
	}
	
	
	/**
	* Generates a SELECT SUM(field) portion of a query
	* 取得指定欄位 "加總" 值
	*
	* @param	string
	* @param	string
	* @return	object
	*/
	function select_sum($field, $alias)
	{
		if ( ! is_string($field) OR $field == '') {
			trigger_error(__CLASS__."::".__FUNCTION__." need field", E_USER_WARNING);
		}

		$alias = ($alias != '') ? $alias : $field;
		
		$str = 'SUM('.trim($field).') AS '.trim($alias);
		$this->select($str);
		return $this;
	}
	
	
	/**
	* Generates a SELECT AVG(field) portion of a query
	* 取得指定欄位 "平均" 值
	*
	* @param	string
	* @param	string
	* @return	object
	*/
	function select_avg($field, $alias)
	{
		if ( ! is_string($field) OR $field == '') {
			trigger_error(__CLASS__."::".__FUNCTION__." need field", E_USER_WARNING);
		}

		$alias = ($alias != '') ? $alias : $field;
		
		$str = 'AVG('.trim($field).') AS '.trim($alias);
		$this->select($str);
		return $this;
	}
	
	
	/**
	* Set s the SELECT value
	* 設定 SELECT 條件
	*
	* @param	string
	* @return	object
	*/
	function select($value)
	{
		$this->ar_select[] = $val;
		return $this;
	}
	
	
	/**
	* DISTINCT
	*
	* Sets a flag which tells the query string compiler to add DISTINCT
	*
	* @access	public
	* @param	bool
	* @return	object
	*/
	function distinct($val = TRUE)
	{
		$this->ar_distinct = (is_bool($val)) ? $val : TRUE;
		return $this;
	}
	
	
	/**
	* Set s the WHERE value
	* 設定 WHERE 條件
	*
	* @param	string
	* @return	object
	*/
	function where($value)
	{
		if ($value != '') {
			if (is_array($value)) {
				$this->wheres($value);
			} else {
				$this->ar_where[] = $value;
			}
		}
		return $this;
	}
	
	function wheres($arr)
    {
        foreach ($arr as $val) {
            $this->where($val);
        }
		return $this;
    }
	
	/**
	* Sets the GROUP BY value
	* 設定 GROUP BY 條件
	*
	* @param	string
	* @return	object
	*/
	function group_by($value)
	{
		if (is_string($value)) {
			$value = explode(',', $value);
		}
		
		foreach ($value as $val) {
			$val = trim($val);
			
			if ($val != '') {
				$this->ar_groupby[] = $val;
			}
		}
		return $this;
	}
	
	
	/**
	* Sets the HAVING values
	* 設定 HAVING 條件
	*
	* @param	string
	* @return	object
	*/
	function having($value)
	{
		if (is_string($value)) {
			$value = explode(',', $value);
		}
		
		foreach ($value as $val) {
			$val = trim($val);
			
			if ($val != '') {
				$this->ar_having[] = $val;
			}
		}
		return $this;
	}
	
	
	/**
	* Sets the  ORDER BY value
	* 設定 ORDER BY  條件
	*
	* @access	public
	* @param	string
	* @param 	string
	* @return	object
	*
	* $handler->order_by('created DESC')  =  " ORDER BY created DESC "
	* $handler->order_by('created')  =  " ORDER BY created ASC "
	*/
	function order_by($orderby, $direction = '')
	{
		if (trim($direction) != '')
		{
			$direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC';
		}
		
		$orderby_clause = $orderby.$direction;
		
		$this->ar_orderby[] = $orderby_clause;
		
		return $this;
	}
	
	
	/**
	* Sets the LIMIT value
	*
	* @access	public
	* @param	integer	the limit value
	* @param	integer	the offset value
	* @return	object
	*/
	function limit($value, $offset = '')
	{
		$this->ar_limit = intval($value);
		
		if ($offset != '') {
			$this->ar_offset = intval($offset);
		}
		return $this;
	}
	
	
	/**
	* Sets the OFFSET value
	*
	* @access	public
	* @param	integer	the offset value
	* @return	object
	*/
	function offset($offset)
	{
		$this->ar_offset = intval($offset);
		return $this;
	}
	
	
	/**
	* Check token
	*
	* $handler->token()->deleteRow($_POST['id'])
	* $handler->token()->where('status = 1')->updateAll($_POST)
	*/
	function token()
	{
		if( substr( XOOPS_VERSION , 6 , 3 ) > 2.0 ) {
			global $GLOBALS;
			// XOOPS 2.1+
			if (!$GLOBALS['xoopsSecurity']->check()) {
				return FALSE;
			}
		} else {
			// XOOPS 2.0.x
			$token_handler = new XoopsMultiTokenHandler();
			if(!$token_handler->autoValidate($token_name)) {
				return FALSE;
			}
		}
		return $this;
	}
	
	
	/**
	* Combination query string
	* 組合查詢的 sql 字串
	*
	* @param     string
	* @return	string
	*/
	function compileSQL($sql_override = FALSE, $sql_reset = TRUE)
	{
		$sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';
		
		$sql .= (count($this->ar_select) == 0) ? '*' : implode(', ', $this->_filter_table_aliases($this->ar_select));
		
		$sql .= ' FROM '.$this->tableName;
		
		if ($sql_override !== FALSE)
		{
			$sql = $sql_override;
		}
		
		if (count($this->ar_where) > 0) {
			$sql .= "\nWHERE ";
			$sql .= implode("\nAND ", $this->ar_where);
		}
		
		if (count($this->ar_groupby) > 0) {
			$sql .= "\nGROUP BY ";
			$sql .= implode(', ', $this->ar_groupby);
		}
		
		if (count($this->ar_having) > 0) {
			$sql .= "\nHAVING ";
			$sql .= implode(', ', $this->ar_having);
		}
		
		$sql .= "\nORDER BY ";
		if (count($this->ar_orderby) > 0) {
			$sql .= implode(', ', $this->ar_orderby);
		} else {
			$sql .= $this->orderBy;
		}
			
		if (is_numeric($this->ar_limit) AND !empty($this->ar_limit)) {
			$sql .= "\nLIMIT ".$this->ar_limit;
			if (is_numeric($this->ar_offset)) {
				$sql .= ", ".$this->ar_offset;
			}
		}
		
		if ($sql_reset) {
			$this->resetSQL();
		}
		
		return $sql;
	}
	
	function renderSQL()
	{
		$sql = $this->compileSQL();
		return $sql;
	}
	/**
	* Reset query string
	* after all action clean sql order
	* 在每次動作後清除查詢的 sql 字串
	*/
	function resetSQL()
	{
		$default = array(
			'ar_distinct'  => FALSE,
			'ar_select'    => array(),
			'ar_where'	   => array(),
			'ar_groupby'   => array(),
			'ar_having'    => array(),
			'ar_orderby'   => array(),
			'ar_limit'     => FALSE,
			'ar_offset'	   => FALSE,
			'ar_data'      => array()
		);
		
		foreach ($default as $item => $value) {
			$this->$item = $value;
		}
	}
	
	
	/**
	* Set error message
	* 設定錯誤訊息
	*/
	function setErrors($err_str)
	{
		if (is_array($err_str)) {
			$this->_errors = array_merge($this->_errors, $err_str);
		} else {
			$this->_errors[] = trim($err_str);
		}
	}
	
	/**
	* Show error message
	* 顯示錯誤訊息
	*/
	function getErrors()
	{
		return $this->_errors;
	}
	
	/**
	* Return error message
	* echo $handler->getHtmlErrors()
	* 取得錯誤訊息
	*/
	function getHtmlErrors()
	{
		$ret = '<h4>Errors</h4>';
		if (!empty($this->errors)) {
			foreach ($this->errors as $error) {
				$ret .= '<pre>'.$error.'</pre>';
			}
		} else {
			return FALSE;
		}
		return $ret;
	}
	
}
?>